package com.mrd.gtimp.base.service;

import com.alibaba.fastjson.JSONObject;
import com.mrd.base.BaseBiz;
import com.mrd.exception.QueryException;
import com.mrd.exception.SaveException;
import com.mrd.framework.utils.SpringUtils;
import com.mrd.gtimp.base.entity.Company;
import com.mrd.gtimp.business.entity.DeclarationPort;
import com.mrd.gtimp.business.service.DeclarationPortBiz;
import com.mrd.spring.repository.BaseRepository;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

/**
 * <p>
 * application name: gtimp
 * <p>
 * copyright: Copyright@2012 明睿达科技有限公司
 */

/**
 * @author qiul
 */
@Service
public class CompanyBiz extends BaseBiz<Company> {

    @Resource
    // 该处指定注入BaseRepository的dao对象
    @Qualifier("companyDao")
    public void setBaseRepository(BaseRepository<Company, String> baseRepository) {
        super.baseRepository = baseRepository;
    }

    @Resource
    private DeclarationPortBiz declarationPortBiz;

    public List<JSONObject> cusCompanyByKey(Pageable pageable, String key) throws QueryException {
        List<JSONObject> list = new ArrayList<>();
        String sql = " select t.com_id,t.com_name from T_SYS_COMPANY t" +
                " where t.com_type='CL'" +
                " and t.com_validate=1" +
                " and t.com_state='0'" +
                " and t.com_isabled='0'";
        Map<String, Object> paramaters = null;
        if (StringUtils.isNotEmpty(key)) {
            sql += " and t.com_name like :com_name";
            paramaters = Collections.singletonMap("com_name", "%" + key + "%");
        }
        List<Object> objectsts = findObjectBySql(sql, pageable.getPageNumber() + 1, pageable.getPageSize(), paramaters);
        for (Object object : objectsts) {
            Object[] obj = (Object[]) object;
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", String.valueOf(obj[0]));
            jsonObject.put("name", String.valueOf(obj[1]));
            list.add(jsonObject);
        }
        return list;
    }

    public List<JSONObject> supCompanyByKey(Pageable pageable, String key) throws QueryException {
        List<JSONObject> list = new ArrayList<>();
        String sql = " select t.com_id,t.com_name,t.port,t.port_code,t.com_customs_code from T_SYS_COMPANY t " +
                " where t.com_type='CC'" +
                " and t.com_validate=1" +
                " and t.com_state='0'" +
                " and t.com_isabled='0'";
        Map<String, Object> paramaters = null;
        if (StringUtils.isNotEmpty(key)) {
            sql += " and t.com_name like :com_name";
            paramaters = Collections.singletonMap("com_name", "%" + key + "%");
        }
        List<Object> objectsts = findObjectBySql(sql, pageable.getPageNumber() + 1, pageable.getPageSize(), paramaters);
        for (Object object : objectsts) {
            Object[] obj = (Object[]) object;
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", String.valueOf(obj[0]));
            jsonObject.put("name", String.valueOf(obj[1]));
            jsonObject.put("port", String.valueOf(obj[2]));
            jsonObject.put("port_code", String.valueOf(obj[3]));
            jsonObject.put("com_customs_code", String.valueOf(obj[4]));
            list.add(jsonObject);
        }
        return list;
    }

    /**
     * 查询供应商 报关公司
     */
    public List<JSONObject> supCom(Pageable pageable, boolean isExport, String code)
            throws QueryException {
        List<JSONObject> list = new ArrayList<>();
//        String hql = "select t.id,t.name,t.port,t.port_code,t.com_customs_code,p.hasSO " +
//                " from com.mrd.gtimp.base.entity.Company t " +
//                " left join com.mrd.gtimp.business.entity.DeclarationPort p on t.port_code=p.portCode"
//                + " right join com.mrd.gtimp.business.entity.DeclarationOffer o "
//                + " on o.declaration_company_id = t.id" + " and o.price_begin_time < sysdate "
//                + " and o.price_end_time > sysdate and o.state = 'OS05' " + " where t.type='CC' "
//                + " and t.validate=1 " + " and t.state='0' " + " and t.isabled='0' ";
//

        String sql = " select t.com_id,t.com_name,t.port,t.port_code,t.com_customs_code,p.has_SO" +
                " from t_sys_company t," +
                " t_biz_declaration_port p ,T_GEN_PROTOCOL_UNITE o" +
                " where t.PORT_CODE=P.PORT_CODE" +
                " and O.SUPPLIER_ID=T.com_id" +
                " and T.com_validate='1' and T.com_state='0' and T.com_isabled='0' and t.com_type='CC'" +
                " and O.STATUS='1'" +
                " and o.EFTIME_START < sysdate and o.EFTIME_END > sysdate";
        Map<String, Object> paramaters = null;
        if (StringUtils.isNotEmpty(code)) {
            sql += " and t.port_code = :port_code";
            paramaters = Collections.singletonMap("port_code", code);
        }
//        if (isExport) {
//            // 出口报价
//            hql += " and o.declaration_amount is not null ";
//            // hql = String.format(hql, "o.declaration_amount as amount");
//        } else {
//            // 进口报价
//            // hql = String.format(hql, "o.imp_declaration_amount as amount");
//            hql += " and o.imp_declaration_amount is not null ";
//        }

        sql += " order by p.PORT_SORT";
//        List<Object> objects = findObjectByHql(hql, pageable.getPageNumber() + 1, pageable
//                .getPageSize(), paramaters);
        List<Object> objects = this.findObjectBySql(sql, pageable.getPageNumber() + 1, pageable
                .getPageSize(), paramaters);
        for (Object object : objects) {
            Object[] obj = (Object[]) object;
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("id", String.valueOf(obj[0]));
            jsonObject.put("name", String.valueOf(obj[1]));
            jsonObject.put("port", String.valueOf(obj[2]));
            jsonObject.put("port_code", String.valueOf(obj[3]));
            jsonObject.put("com_customs_code", String.valueOf(obj[4]));
            jsonObject.put("hasSO", String.valueOf(obj[5]));
            // jsonObject.put("amount", String.valueOf(obj[5]));
            // jsonObject.put("container_amount", String.valueOf(obj[6]));
            list.add(jsonObject);
        }
        return list;
    }

    /**
     * 查询供应商 报关公司
     */
    public List<Company> supCom(Pageable pageable) throws QueryException {
        List<Company> list = new ArrayList<>();
        String hql = "select t.id,t.name,t.port,t.port_code,t.com_customs_code "
                + " from com.mrd.gtimp.base.entity.Company t" + " where t.type='CC' "
                + " and t.validate=1 " + " and t.state='0' " + " and t.isabled='0' "
                + " and t.com_customs_code is not null";

        List<Object> objects = findObjectByHql(hql, pageable.getPageNumber() + 1, pageable
                .getPageSize(), null);
        for (Object object : objects) {
            Object[] obj = (Object[]) object;
            Company company = new Company();
            company.setId(String.valueOf(obj[0]));
            company.setName(String.valueOf(obj[1]));
            company.setPort(String.valueOf(obj[2]));
            company.setPort_code(String.valueOf(obj[3]));
            company.setCom_customs_code(String.valueOf(obj[4]));
            list.add(company);
        }
        return list;
    }

    /**
     * 保存的同时保存DeclarationPort表
     */
    @Override
    public void saveOrupdateEntity(Company entity) throws SaveException {
        super.saveOrupdateEntity(entity);
        DeclarationPort declarationPort = declarationPortBiz.findEntityByPortCode(entity.getPort_code());
        if (declarationPort == null) {
            declarationPort = new DeclarationPort();
            declarationPort.setId(null);
            declarationPort.setPortCode(entity.getPort_code());
            declarationPort.setPortName(entity.getPort());
            declarationPortBiz.saveOrupdateEntity(declarationPort);
        }

    }

    /**
     * 通过用户ID获取对应公司信息
     *
     * @param uid 用户ID
     */
    public Company getCompanyByUId(String uid) {
        String querCompanySql = "select c.COM_ID,c.COM_NAME,c.COM_CONTACTOR,c.COM_PHONE,c.COM_MOBILE,c.COM_FAX,c.COM_VALIDATE,c.PORT,c.PORT_CODE,c.COM_CODE from t_sys_company c where exists (select 1 from t_sys_com_dept cd inner join t_sys_dept_user du on du.dept_id = cd.dept_id and du.u_id = ? where cd.com_id = c.com_id)";
        JdbcTemplate jdbcTemplate = SpringUtils.getBean(JdbcTemplate.class);
        List<Company> companyList = jdbcTemplate.query(querCompanySql, new RowMapper<Company>() {
            @Override
            public Company mapRow(ResultSet rs, int rowNum) throws SQLException {
                Company c = new Company();
                c.setId(rs.getString("COM_ID"));
                c.setName(rs.getString("COM_NAME"));
                c.setContactor(rs.getString("COM_CONTACTOR"));
                c.setPhone(rs.getString("COM_PHONE"));
                c.setMobile(rs.getString("COM_MOBILE"));
                c.setFax(rs.getString("COM_FAX"));
                c.setValidate(rs.getString("COM_VALIDATE"));
                c.setPort(rs.getString("PORT"));
                c.setPort_code(rs.getString("PORT_CODE"));
                c.setCom_code(rs.getString("COM_CODE"));
                return c;
            }
        }, uid);
        if (CollectionUtils.isNotEmpty(companyList)) {
            return companyList.get(0);
        }
        return null;
    }
}
